package com.example.demo.dao;

import com.example.demo.entity.BookVo;

import javax.servlet.annotation.WebServlet;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class BookDao {

    public List<BookVo> getBooks() throws Exception {
        List<BookVo> list = new ArrayList<BookVo>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con = DriverManager.getConnection(DB.JDBC_URL, DB.JDBC_USER, DB.JDBC_PASSWORD);
            ps = con.prepareStatement("select * from Book ");
            rs = ps.executeQuery();
            while (rs.next()) {
                BookVo b = new BookVo();
                b.setBookid((rs.getInt("BookID")));
                b.setTitle((rs.getString("Title")));
                b.setAuthor(rs.getString("Author"));
                b.setPrice(rs.getInt("Price"));
                b.setStock(rs.getInt("Stock"));
                list.add(b);
            }
        }catch (Exception e){
            e.printStackTrace();
            throw new Exception("查询失败");
        }finally{
            if (rs != null) rs.close();
            if (ps != null) ps.close();

        }
        return list;
    }
    public BookVo findBookId(Integer id) throws Exception {
        BookVo b = null;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con = DriverManager.getConnection(DB.JDBC_URL, DB.JDBC_USER, DB.JDBC_PASSWORD);
            ps = con.prepareStatement("select * from Book where BookID=? ");
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                b = new BookVo();
                b.setBookid(rs.getInt("BookID"));
                b.setTitle(rs.getString("Title"));
                b.setPrice(rs.getInt("Price"));
                b.setAuthor(rs.getString("Author"));
                b.setStock(rs.getInt("Stock"));
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("查询失败");
        } finally {
            if (rs != null) rs.close();
            if (ps != null) ps.close();
            if (con != null) con.close();
        }
        return b;
    }


    public List<BookVo> findBooks(String name) throws Exception {
        List<BookVo> list = new ArrayList<BookVo>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con = DriverManager.getConnection(DB.JDBC_URL, DB.JDBC_USER, DB.JDBC_PASSWORD);
            ps = con.prepareStatement("select * from Book where Title like ? ");
            ps.setString(1, "%" + name + "%");
            rs = ps.executeQuery();
            while (rs.next()) {
                BookVo b = new BookVo();
                b.setBookid((rs.getInt("BookID")));
                b.setTitle((rs.getString("Title")));
                b.setAuthor(rs.getString("Author"));
                b.setPrice(rs.getInt("Price"));
                b.setStock(rs.getInt("Stock"));
                list.add(b);
            }
        }catch (Exception e){
            e.printStackTrace();
        throw new Exception("查询失败");
    }finally{
        if (rs != null) rs.close();
        if (ps != null) ps.close();

    }
    return list;
}

public void saveBook(BookVo b) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con = DriverManager.getConnection(DB.JDBC_URL, DB.JDBC_USER, DB.JDBC_PASSWORD);
            ps = con.prepareStatement("insert into Book(Title,Author,Price,Stock) values(?,?,?,?)");
            ps.setString(1, b.getTitle());
            ps.setString(2, b.getAuthor());
            ps.setDouble(3, b.getPrice());
            ps.setInt(4, b.getStock());
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            throw new Exception("保存失败");
        }finally{
            if (ps != null) ps.close();
            if (con != null) con.close();

        }
}

public void updateBook(BookVo b) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con = DriverManager.getConnection(DB.JDBC_URL, DB.JDBC_USER, DB.JDBC_PASSWORD);
            ps = con.prepareStatement("update Book set Title=?,Author=?,Price=?,Stock=? where BookID=?");
            ps.setString(1, b.getTitle());
            ps.setString(2, b.getAuthor());
            ps.setDouble(3, b.getPrice());
            ps.setInt(4, b.getStock());
            ps.setInt(5, b.getBookid());
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            throw new Exception("更新失败");
        }finally {
            if (ps != null) ps.close();
            if (con != null) con.close();

        }
}

public void deleteBookByID(Integer id) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con = DriverManager.getConnection(DB.JDBC_URL, DB.JDBC_USER, DB.JDBC_PASSWORD);
            ps = con.prepareStatement("delete from Book where BookID=?");
            ps.setInt(1, id);
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            throw new Exception("删除失败");
        }finally {
            if (ps != null) ps.close();
            if (con != null) con.close();

        }
    }
}